package com.course.daoImpl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import com.course.bean.AnnouncementTo;
import com.course.bean.CourseTo;
import com.course.daoI.CourseDAOI;
import com.course.dbutil.DBConnectionFactory;
import com.course.dbutil.SqlConstants;
import com.course.util.DateWrapper;

public class CourseDaoImpl implements CourseDAOI{
	
	
	PreparedStatement pstmt,pstmt1;
	Statement stmt;
	ResultSet rs,rs1;
	Connection con;
	public CourseDaoImpl(){
		con = DBConnectionFactory.getConnection();	
		}
	public boolean insertCourse(CourseTo cb)
	{
		boolean flag=true;
		
		try{
			pstmt=con.prepareStatement(SqlConstants._INSERT_COURSE);
			
    	pstmt.setString(1, cb.getCname());
    	pstmt.setString(2,cb.getCdescription());
		pstmt.setString(3, "activated");
		
		int insert=pstmt.executeUpdate();
		
		if(insert>0)
        {
        
         con.commit();
        }
        else
        {
       	 flag=false;
       	 con.rollback();
        }

   } 
   
   catch (SQLException e) 
   {
       e.printStackTrace();
       flag=false;
       try 
       {
           con.rollback();
       } 
       catch (SQLException se) 
       {
           se.printStackTrace();
       }
   }
   
   finally
	{
	 try{
		 if(con!=null)
			 con.close();				 
	 }
	 catch(SQLException e){}
	}
return flag;
}
			
			
			
  public boolean deleteCourse(int courseid)
  {
	  
	  boolean flag=true;
	  
	  try{
		  
		 
		  pstmt=con.prepareStatement(SqlConstants._DELETE_COURSE);
		  pstmt.setInt(1, courseid);
		  
		  
		  
		  int delete=pstmt.executeUpdate();
			
			
			if(delete>0)
		    {
		    
		     con.commit();
		    }
		    else
		    {
		   	 flag=false;
		   	 con.rollback();
		    }

		} 

		catch (SQLException e) 
		{
		   e.printStackTrace();
		   flag=false;
		   try 
		   {
		       con.rollback();
		   } 
		   catch (SQLException se) 
		   {
		       se.printStackTrace();
		   }
		}

		finally
		{
		 try{
			 if(con!=null)
				 con.close();				 
		 }
		 catch(SQLException e){}
		}
		return flag;
		}
  
  
  
  
  public Vector<CourseTo> viewCourse()
  {
	  Vector<CourseTo> vcb=null;
	  
	  try{
		  
		 pstmt=con.prepareStatement(SqlConstants._VIEW_COURSE);
		  
		  rs=pstmt.executeQuery();
		  
		  vcb=new Vector<CourseTo>();
		  
		  while(rs.next())
		  {
			  
			  CourseTo cb=new CourseTo();
			  
			  cb.setCourseid(rs.getInt(1));
			  cb.setCname(rs.getString(2));
			  cb.setCdescription(rs.getString(3));
			  cb.setCdate(rs.getString(4));
			  cb.setCstatus(rs.getString(5));
			  
			  
			  vcb.add(cb);
			 }
		  
   }
	  catch (SQLException e) {
	
		  e.printStackTrace();
	}
	   
	return vcb;  	  
  }

  
  public Vector<CourseTo> viewCourseAt(int cid)
  {
	  Vector<CourseTo> vcb=null;
	  
	  try{
		  
		 pstmt=con.prepareStatement(SqlConstants._VIEW_COURSE_AT);
		  
		  
		  pstmt.setInt(1, cid);
		  rs=pstmt.executeQuery();
		  
		  vcb=new Vector<CourseTo>();
		  
		  while(rs.next())
		  {
			  
			  CourseTo cb=new CourseTo();
			  
			  cb.setCourseid(rs.getInt(1));
			  cb.setCname(rs.getString(2));
			  cb.setCdescription(rs.getString(3));
			  cb.setCdate(rs.getString(4));
			  cb.setCstatus(rs.getString(5));
			  
			  
			  vcb.add(cb);
			 }
		  
   }
	  catch (SQLException e) {
	
		  e.printStackTrace();
	}
	   
	return vcb;  	  
  }

  
  
  
  public boolean updateCourse(CourseTo cb)
  {
	  boolean flag=true;
	  try{
		  
		  
		  pstmt=con.prepareStatement(SqlConstants._UPDATE_COURSE);
		  
		  pstmt.setInt(1, cb.getCourseid());
		  pstmt.setString(2, cb.getCname());
		  pstmt.setString(3, cb.getCdescription());
		  pstmt.setString(4, DateWrapper.parseDate(cb.getCdate()));
		  pstmt.setString(5, cb.getCstatus());
		  pstmt.setInt(6, cb.getCourseid());
		  
		  
		  int update=pstmt.executeUpdate();
			
			if(update>0)	   
			{
		    
		     con.commit();
		    }
		    else
		    {
		   	 flag=false;
		   	 con.rollback();
		    }

		} 

		catch (SQLException e) 
		{
		   e.printStackTrace();
		   flag=false;
		   try 
		   {
		       con.rollback();
		   } 
		   catch (SQLException se) 
		   {
		       se.printStackTrace();
		   }
		}

		finally
		{
		 try{
			 if(con!=null)
				 con.close();				 
		 }
		 catch(SQLException e){}
		}
		return flag;
		}
  
  
  public boolean insertAnnouncement(AnnouncementTo ab)
  {
	  
	  boolean flag=true;
	  int uid=0;
	  
	  try{
		  
		  pstmt=con.prepareStatement(SqlConstants._INSERT_ANNOUNCEMENT);
		  
		  int cid=ab.getCourseid();
		  
		  System.out.println("in DAo class courseid is........."+cid);
		  String announcement=ab.getDescription();
		  String login=ab.getLogin();
		  
		  stmt=con.createStatement();
		  rs=stmt.executeQuery("select userid from userdetails where loginid="+"'"+login+"'");
		  
		  if(rs.next())
		  {
			 uid=rs.getInt(1);
		  }
		  
		  pstmt.setInt(1, cid);
		  pstmt.setInt(2, uid);
		  pstmt.setString(3, announcement);
		  
		  int insert=pstmt.executeUpdate();
			
			if(insert>0)
	        {
	        
	         con.commit();
	        }
	        else
	        {
	       	 flag=false;
	       	 con.rollback();
	        }

	   } 
	   
	   catch (SQLException e) 
	   {
	       e.printStackTrace();
	       flag=false;
	       try 
	       {
	           con.rollback();
	       } 
	       catch (SQLException se) 
	       {
	           se.printStackTrace();
	       }
	   }
	   
	   finally
		{
		 try{
			 if(con!=null)
				 con.close();				 
		 }
		 catch(SQLException e){}
		}
	return flag;
	}
  
  
  
  public Vector<AnnouncementTo> viewAnnouncement(int cid)
  {
	  Vector<AnnouncementTo> vab=null;
	  String ctitle="";
	  String fname="";
	  String lname="";
	  String email="";
	  String fax="";
	  
	  try{
		  
		  pstmt=con.prepareStatement(SqlConstants._VIEW_ANNOUNCEMENT_AT);
		  
		  pstmt.setInt(1,cid);
		  
		 rs=pstmt.executeQuery();
		 
		 vab=new Vector<AnnouncementTo>();
		 
		 while(rs.next())
		 {
			 
			 int aid=rs.getInt(1);
			 int courseid=rs.getInt(2);
			 int uid=rs.getInt(3);
			 String description=rs.getString(4);
			 String adate=rs.getString(5);
			 
			 pstmt1=con.prepareStatement(SqlConstants._GET_COURSE_TITLE);
				pstmt1.setInt(1, courseid);
				
				rs=pstmt1.executeQuery();
				if(rs.next())
				{
					
				   ctitle=rs.getString(1);
					}
			 
			 stmt=con.createStatement();
			 
			 rs1=stmt.executeQuery("select firstname,lastname,emailid,faxno from userdetails where userid="+uid);
				
			 if(rs1.next())
			 {
				  fname=rs1.getString(1);
				  lname=rs1.getString(2);
				  email=rs1.getString(3);
				  fax=rs1.getString(4);
				  }
			 
			 
			 AnnouncementTo ab=new AnnouncementTo();
			 
			 
			 ab.setAdate(adate);
			 ab.setAid(aid);
			 ab.setDescription(description);
			 ab.setCourseid(courseid);
			 ab.setUserid(uid);
			 ab.setCtitle(ctitle);
			 ab.setFax(fax);
			 ab.setFname(fname);
			 ab.setLname(lname);
			 ab.setEmail(email);
			 
			vab.add(ab); 
			 
			}
		  
   }
	  catch (SQLException e) {
	e.printStackTrace();
	}
	  
	 return vab; 
	  
  }
  
  
  public Vector<AnnouncementTo> viewAllAnnouncement()
  {
	  Vector<AnnouncementTo> vab=null;
	  String ctitle="";
	  String fname="";
	  String lname="";
	  String email="";
	  String fax="";
	  
	  try{
		  
		  pstmt=con.prepareStatement(SqlConstants._VIEW_ANNOUNCEMENTS);
		  
		 
		  
		 rs=pstmt.executeQuery();
		 
		 vab=new Vector<AnnouncementTo>();
		 
		 while(rs.next())
		 {
			 
			 int aid=rs.getInt(1);
			 int courseid=rs.getInt(2);
			 int uid=rs.getInt(3);
			 String description=rs.getString(4);
			 String adate=rs.getString(5);
			 
			 
		
				pstmt1=con.prepareStatement(SqlConstants._GET_COURSE_TITLE);
				pstmt1.setInt(1, courseid);
				
				rs=pstmt1.executeQuery();
				if(rs.next())
				{
					
				   ctitle=rs.getString(1);
					}
			 
			 stmt=con.createStatement();
			 
			 rs1=stmt.executeQuery("select firstname,lastname,emailid,faxno from userdetails where userid="+uid);
				
			 if(rs1.next())
			 {
				  fname=rs1.getString(1);
				  lname=rs1.getString(2);
				  email=rs1.getString(3);
				  fax=rs1.getString(4);
				  }
			 
			 
			 AnnouncementTo ab=new AnnouncementTo();
			 
			 
			 ab.setAdate(adate);
			 ab.setAid(aid);
			 ab.setDescription(description);
			 ab.setCourseid(courseid);
			 ab.setUserid(uid);
			 ab.setCtitle(ctitle);
			 ab.setFax(fax);
			 ab.setFname(fname);
			 ab.setLname(lname);
			 ab.setEmail(email);
			 
			vab.add(ab); 
			 
			}
		  
   }
	  catch (SQLException e) {
	e.printStackTrace();
	}
	  
	 return vab; 
	  
  }
  
  
  public boolean insertMaterial(CourseTo cb) throws FileNotFoundException
  {
	  
	  boolean flag=true;
	  int uid=0;
	  
	  
	  try{
		  
		  pstmt=con.prepareStatement(SqlConstants._INSERT_MATERIAL);
		  
		  
		  int cid=cb.getCourseid();
		  System.out.println("in DAo courseid is,,,,,,,,,,,,,,,,,,,"+cid);
		  String login=cb.getLogin();
		  String material=cb.getMaterial();
		 String mname=cb.getMname();
		  
		  
		  stmt=con.createStatement();
		  rs=stmt.executeQuery("select userid from userdetails where loginid="+"'"+login+"'");
		  
		  System.out.println("in DAo login is ....,,,,,,,,,,,"+login);
		  
		  if(rs.next())
		  {
			 uid=rs.getInt(1);
			 
			 System.out.println("in Dao userid is.............."+uid);
		  }
		  
		  
		  pstmt.setInt(1, cid);
		  pstmt.setInt(2, uid);
		 
		  File file=new File(material);
		  FileInputStream fis=new FileInputStream(file);
		  pstmt.setBinaryStream(3,fis ,(int)file.length());
		  
		  pstmt.setString(4, mname);
		  
		  int insert=pstmt.executeUpdate();
			
			if(insert>0)
	        {
	        
	         con.commit();
	        }
	        else
	        {
	       	 flag=false;
	       	 con.rollback();
	        }

	   } 
	   
	   catch (SQLException e) 
	   {
	       e.printStackTrace();
	       flag=false;
	       try 
	       {
	           con.rollback();
	       } 
	       catch (SQLException se) 
	       {
	           se.printStackTrace();
	       }
	   }
	   
	   finally
		{
		 try{
			 if(con!=null)
				 con.close();				 
		 }
		 catch(SQLException e){}
		}
	return flag;
	}
  
  
  
  public Vector<CourseTo> viewMaterial(String path,int cid)
  {
	  
	  Vector<CourseTo> vcb=null;
	  
	  try{
		  
		  pstmt=con.prepareStatement(SqlConstants._VIEW_MATERIAL );
		  
		  
		  pstmt.setInt(1, cid);
		  
		  rs=pstmt.executeQuery();
		  
		  vcb=new Vector<CourseTo>();
		  
		  while(rs.next())
		  {
			  
			  int mid=rs.getInt(1);
			  
			  Blob b=rs.getBlob(2);
			  byte b1[]=b.getBytes(1,(int)b.length());
			   
			  OutputStream fout=new FileOutputStream(path+"/"+mid+ ".doc");
			 // System.out.println(fout+"*********************");
			    fout.write(b1);
			  
			    CourseTo cb=new CourseTo();
			  
			  cb.setMid(mid);
			  cb.setMaterial(".doc");
			  
			  
			  vcb.add(cb);
			  
			  
		  }
		  
  }
	  catch (Exception e) {
	e.printStackTrace();
	}
	  
	  
	  
	 return vcb; 
	  
	  
	  
	  
  }
  
  
  public Vector<CourseTo> getFaculty(int cid)
  {
	  
	  Vector<CourseTo> vcb=null;
	  
	  try{
		  
		  pstmt=con.prepareStatement(SqlConstants._GET_FACULTY );
		  
		  pstmt.setInt(1, cid);
		  rs=pstmt.executeQuery();
		  
		  vcb=new Vector<CourseTo>();
		  String ctitle="";
		  
		  while(rs.next())
		  {
			  
			  int userid=rs.getInt(1);
			  String fname=rs.getString(2);
			  String lname=rs.getString(3);
			  String email=rs.getString(4);
			  String fax=rs.getString(5);
			  int cid1=rs.getInt(6);
			  String login=rs.getString(7);
			  
			  
			  
			 
				pstmt1=con.prepareStatement(SqlConstants._GET_COURSE_TITLE);
				pstmt1.setInt(1, cid1);
				
				rs1=pstmt1.executeQuery();
				if(rs1.next())
				{
					
				   ctitle=rs1.getString(1);
					}
			  
			  
				CourseTo cb=new CourseTo();
			  
			cb.setFax(fax);
			cb.setFname(fname);
			cb.setLname(lname);
			cb.setUserid(userid);
			cb.setEmail(email);
			cb.setCname(ctitle);
			cb.setCourseid(cid1);
			 cb.setLogin(login); 
			  
			vcb.add(cb);  
			  
		  }
		  
   }
	  catch (SQLException e) {
		e.printStackTrace();
	}
	  
	return vcb;
	  
	}
  
  
  public Vector<CourseTo> getStudent(int cid)
  {
	  
	  Vector<CourseTo> vcb=null;
	  
	  try{
		  
		  pstmt=con.prepareStatement(SqlConstants._GET_STUDENT );
		  
		  pstmt.setInt(1, cid);
		  rs=pstmt.executeQuery();
		  
		  vcb=new Vector<CourseTo>();
		  String ctitle="";
		  
		  while(rs.next())
		  {
			  
			  int userid=rs.getInt(1);
			  String fname=rs.getString(2);
			  String lname=rs.getString(3);
			  String email=rs.getString(4);
			  String fax=rs.getString(5);
			  int cid1=rs.getInt(6);
			  String login=rs.getString(7);
			  
			  
			  
			 
				pstmt1=con.prepareStatement(SqlConstants._GET_COURSE_TITLE);
				pstmt1.setInt(1, cid1);
				
				rs1=pstmt1.executeQuery();
				if(rs1.next())
				{
					
				   ctitle=rs1.getString(1);
					}
			  
			  
				CourseTo cb=new CourseTo();
			  
			cb.setFax(fax);
			cb.setFname(fname);
			cb.setLname(lname);
			cb.setUserid(userid);
			cb.setEmail(email);
			cb.setCname(ctitle);
			cb.setCourseid(cid1);
			 cb.setLogin(login); 
			  
			vcb.add(cb);  
			  
		  }
		  
   }
	  catch (SQLException e) {
		e.printStackTrace();
	}
	  
	return vcb;
	  
	}
	
	
	
	
	
	
	
	

}
